package fy.hr.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import fy.config.FyConfig;
import fy.hr.bean.LeaveFormBean;
import fy.hr.constant.OA2HRConstant;
import fy.util.DBUtil;
import fy.util.MyUtil;

public class LeaveForm_WriterDao {
	private static Log log = LogFactory.getLog(LeaveForm_WriterDao.class);

	private final static String INSERT_TBM_LEAVEH = "insert into tbm_leaveh(applier,applydate,applydept,approvestatus,billname,fun_code,isappbill,ishrssbill,leaveid,leavetype,leaveyear,pk_corp,pk_leave_h,ts)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	private final static String INSERT_TBM_LEAVEB = "insert into tbm_leaveb(leavebeginday,leavebegintime,leaveendday,leaveendtime,leavenote,leaveoffstatus,leavepsnorder,leavesumhour,pk_leave_b,pk_leave_h,pk_tbm_psndoc,PSNCODE,ts,logoutleave_note,BILLNAME_NEW, ifapprove) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	private String errorMsg;

	private boolean needRecord;

	private LeaveFormBean leaveBean;

	private List<String> workDayArray;

	private List<String> holidayArray;

	private float leaveDays;

	public float getLeaveDays() {
		return leaveDays;
	}

	public void setLeaveBean(LeaveFormBean leaveBean) {
		this.leaveBean = leaveBean;
	}

	public String getErrorMsg() {
		return errorMsg;
	}

	public boolean isNeedRecord() {
		return needRecord;
	}

	public boolean write(String accountID) {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.connect(FyConfig.getEHR_DB_CONFIG());

			pst = conn
					.prepareStatement("select p.pk_psndoc, p.pk_deptdoc, p.pk_corp, t.pk_tbm_psndoc from bd_psndoc p left join tbm_psndoc t on t.pk_psndoc=p.pk_psndoc where to_date(t.enddate,'yyyy-mm-dd') > sysdate and p.psnclscope in ('0','5') and p.psncode=?");

			pst.setString(1, leaveBean.getMemberCode());

			rs = pst.executeQuery();
			if (rs.next()) {
				String pk_psndoc = rs.getString(1);
				String pk_deptdoc = rs.getString(2);
				String pk_corp = rs.getString(3);
				String pk_tbm_psndoc = rs.getString(4);
				DBUtil.close(rs, pst);

				String startTime = OA2HRConstant.getWorkTime(
						leaveBean.getStartTime(), pk_corp,accountID);
				String endTime = OA2HRConstant.getWorkTime(
						leaveBean.getEndTime(), pk_corp,accountID);

				if (pk_tbm_psndoc != null && !"".equals(pk_tbm_psndoc)) {

					pst = conn
							.prepareStatement("select t.pk_timeitem, t1.restdayorhour, t.timeitemunit   from tbm_timeitem t, TBM_LMHOLIDAY t1, bd_psndoc t2 where (t.dr='0' or t.dr is null) and  trim(t.timeitemname)= ? and t2.psncode = ? and t1.curyear=? and trim(t.fathertimeitem) = 'leaveclass' and t.pk_corp = t2.pk_corp and t.pk_corp=? and t.pk_timeitem = t1.pk_timeitem and t1.pk_psndoc = t2.pk_psndoc");
					// trim(t.timeitemname)= ? and t2.psncode = ? and
					// t1.curyear=?
//					System.err.println("请假类别+:"+leaveBean.getLeaveType());
//					System.err.println("请假编号:"+leaveBean.getMemberCode());
//					System.err.println("请假日期:"+leaveBean.getStartDate().substring(0, 4));
//					System.err.println("请假公司:"+pk_corp);
//					System.err.println("select t.pk_timeitem, t1.restdayorhour, t.timeitemunit   from tbm_timeitem t, TBM_LMHOLIDAY t1, bd_psndoc t2 where (t.dr='0' or t.dr is null) and  trim(t.timeitemname)='"+leaveBean.getLeaveType() +"' and t2.psncode = '"+leaveBean.getMemberCode()+"' and t1.curyear='"+leaveBean.getStartDate().substring(0, 4)+"' and trim(t.fathertimeitem) = 'leaveclass' and t.pk_corp = t2.pk_corp and t.pk_corp='"+pk_corp+"' and t.pk_timeitem = t1.pk_timeitem and t1.pk_psndoc = t2.pk_psndoc");
					pst.setString(1, leaveBean.getLeaveType());
					pst.setString(2, leaveBean.getMemberCode());
					pst.setString(3, leaveBean.getStartDate().substring(0, 4));
					pst.setString(4, pk_corp);
					rs = pst.executeQuery();

					if (rs.next()) {

						String pk_timeitem = rs.getString(1);
						float remainDays = Float.valueOf(rs.getString(2));
						String timeitemunit = rs.getString(3);
						DBUtil.close(rs, pst);

						// 判断用户是否在某个时间段已经请了假
						pst = conn
								.prepareStatement("select count(*) from tbm_leaveb b join tbm_leaveh h on b.pk_leave_h = h.pk_leave_h where h.applier =? and (b.dr='0' or b.dr is null) and (h.dr='0' or h.dr is null) and ((b.leavebeginday < ? or b.leavebeginday = ? and b.leavebegintime <= ?) and (b.leaveendday > ? or b.leaveendday = ? and b.leaveendtime > ?) or (b.leavebeginday < ? or b.leavebeginday = ? and b.leavebegintime < ?) and (b.leaveendday > ? or b.leaveendday = ? and b.leaveendtime >= ?) or (b.leavebeginday > ?  or b.leavebeginday = ? and b.leavebegintime >= ?) and (b.leaveendday < ? or b.leaveendday = ? and b.leaveendtime <= ?))");
						pst.setString(1, pk_psndoc);
						pst.setString(2, leaveBean.getStartDate());
						pst.setString(3, leaveBean.getStartDate());
						pst.setString(4, startTime);
						pst.setString(5, leaveBean.getStartDate());
						pst.setString(6, leaveBean.getStartDate());
						pst.setString(7, startTime);

						pst.setString(8, leaveBean.getEndDate());
						pst.setString(9, leaveBean.getEndDate());
						pst.setString(10, endTime);
						pst.setString(11, leaveBean.getEndDate());
						pst.setString(12, leaveBean.getEndDate());
						pst.setString(13, endTime);

						pst.setString(14, leaveBean.getStartDate());
						pst.setString(15, leaveBean.getStartDate());
						pst.setString(16, startTime);
						pst.setString(17, leaveBean.getEndDate());
						pst.setString(18, leaveBean.getEndDate());
						pst.setString(19, endTime);

						rs = pst.executeQuery();
						boolean isExists = false;
						if (rs.next()) {
							int count = rs.getInt(1);
							isExists = (count > 0);
						}
						if (!isExists) {

							// 查询工作日期
							pst = conn
									.prepareStatement("select calendar from tbm_psncalendar where pk_psndoc=? and calendar between ? and ? and if_rest='N' and (dr='0' or dr is null) order by calendar asc");
							pst.setString(1, pk_psndoc);
							pst.setString(2, leaveBean.getStartDate());
							pst.setString(3, leaveBean.getEndDate());

							rs = pst.executeQuery();

							while (rs.next()) {
								if (workDayArray == null) {
									workDayArray = new ArrayList<String>();
								}
								workDayArray.add(rs.getString(1));
							}
							DBUtil.close(rs, pst);

							// 查询节假日放假
							pst = conn
									.prepareStatement("select rq from tbm_fzqxjb where rq between ? and ? and pk_corp=? and (dr='0' or dr is null) order by rq asc");
							pst.setString(1, leaveBean.getStartDate());
							pst.setString(2, leaveBean.getEndDate());
							pst.setString(3, pk_corp);

							rs = pst.executeQuery();
							while (rs.next()) {
								if (holidayArray == null) {
									holidayArray = new ArrayList<String>();
								}
								holidayArray.add(rs.getString(1));
							}
							DBUtil.close(rs, pst);

							leaveDays = calculateLeaveDays()
									* ("2".equals(timeitemunit) ? 8F : 1F);

							if (leaveDays > remainDays) {
								if (!"调休".equals(leaveBean.getLeaveType())) {
									needRecord = true;
									this.errorMsg = "用户申请的假期天数超过所请假别的节余时间";
								}
							}
							workDayArray = null;
							holidayArray = null;

							// pst = conn
							// .prepareStatement("select t.pk_timeitem, t1.restdayorhour from tbm_timeitem t, TBM_LMHOLIDAY t1, bd_psndoc t2 where t.dr<>'1' and  trim(t.timeitemname)= ? and t2.psncode = ? and t1.curyear=? and trim(t.fathertimeitem) = 'leaveclass' and t.pk_corp = t2.pk_corp  and t.pk_timeitem = t1.pk_timeitem and t1.pk_psndoc = t2.pk_psndoc");

							String pk_leave_h = MyUtil.createUUID();

							String ts = MyUtil.getNowStr();

							conn.setAutoCommit(false);
							// insert into
							// tbm_leaveh(applier,applydate,applydept,approvestatus,billname,fun_code,
							// isappbill,ishrssbill,leaveid,leavetype,leaveyear,pk_corp,pk_leave_h,ts)
							// values(pk_psndoc,日期,部门,'200',流水号,'6017070410',
							// 'N'.'N',流水号,休假类型,年度,制造部,流水号,日期时间);
							pst = conn.prepareStatement(INSERT_TBM_LEAVEH);

							pst.setString(1, pk_psndoc);
							pst.setString(2, leaveBean.getApplicationDate());
							pst.setString(3, pk_deptdoc);
							pst.setString(4, "200");
							pst.setString(5, pk_leave_h);
							pst.setString(6, null);
							pst.setString(7, "N");
							pst.setString(8, "N");
							pst.setString(9, pk_leave_h);
							pst.setString(10, pk_timeitem);
							pst.setString(11, MyUtil.getYear());
							pst.setString(12, pk_corp);
							pst.setString(13, pk_leave_h);
							pst.setString(14, ts);

							pst.execute();

							DBUtil.close(pst);

							String pk_leave_b = MyUtil.createUUID();
							// insert into
							// tbm_leaveb(leavebeginday,leavebegintime,leaveendday,leaveendtime,
							// leavenote,leaveoffstatus,leavepsnorder,leavesumhour,
							// pk_leave_b,pk_leave_h,pk_tbm_psndoc,psncode,ts,logoutleave_note,billanme_new)
							// values(开始日期,开始时间,结束日期,结束时间,休假原因,'2','1',时长,
							// 流水号,流水号,pk_tbm_psndoc,pk_psndoc,pk_psndoc,日期时间,备注,单据号)
							pst = conn.prepareStatement(INSERT_TBM_LEAVEB);
							pst.setString(1, leaveBean.getStartDate());
						
							pst.setString(2, startTime);
						//	System.out.println("*************************"+startTime);
							pst.setString(3, leaveBean.getEndDate());
							pst.setString(4, endTime);
						//	System.out.println("*************************"+endTime);
							pst.setString(
									5,
									MyUtil.getLimitLengthString(
											leaveBean.getReason(), 50, "…"));
							pst.setString(6, "2");
							pst.setString(7, "1");
							pst.setString(8, String.valueOf(leaveDays));
							pst.setString(9, pk_leave_b);
							pst.setString(10, pk_leave_h);
							pst.setString(11, pk_tbm_psndoc);
							pst.setString(12, pk_psndoc);
							pst.setString(13, ts);
							pst.setString(14, "");
							pst.setString(15, leaveBean.getFormNumber());
							pst.setString(16, "N");

							pst.execute();

							conn.commit();
							success = true;
						} else {
							this.errorMsg = "请假的日期重复，请核查后重新提交假条。";
						}
					} else {
						this.errorMsg = "OA的请假类别无法和HR的请假类别匹配，OA请假类别为："
								+ leaveBean.getLeaveType();
					}
				} else {
					this.errorMsg = "请假人在HR系统中无考勤档案";
				}
			} else {
				this.errorMsg = "请假人在HR系统中无考勤档案";
			}

		} catch (Exception e) {
			e.printStackTrace();
			log.error("《请假条》写入失败，表单summaryId:" + leaveBean.getSummaryId()
					+ "，单据编号为：" + leaveBean.getFormNumber(), e);
			this.errorMsg = "写入HR数据库异常";
			DBUtil.rollback(conn);
			needRecord = true;
		} finally {
			DBUtil.setAutoCommit(conn, true);
			DBUtil.close(rs, pst, conn);
		}

		return success;
	}

	private float calculateLeaveDays() {
		if (workDayArray == null) {
			return 0;
		} else {
			float count = 0;
			boolean isHoliday = false;
			for (int i = 0, workDayArraySize = workDayArray.size(); i < workDayArraySize; i++) {
				String workDay = workDayArray.get(i);
				isHoliday = false;
				if (holidayArray != null) {
					for (int j = 0, holidayArraySize = holidayArray.size(); j < holidayArraySize; j++) {
						String holiday = holidayArray.get(j);
						if (workDay.equals(holiday)) {
							isHoliday = true;
							break;
						}
					}
				}
				if (!isHoliday) {
					if (workDay.equals(leaveBean.getStartDate())) {
						if (workDay.equals(leaveBean.getEndDate())) {
							if (leaveBean.getStartTime().equals(
									OA2HRConstant.START_WORK_TIME[0])) {
								if (leaveBean.getEndTime().equals(
										OA2HRConstant.END_WORK_TIME[0])) {
									count = 0.5F;
								} else if (leaveBean.getEndTime().equals(
										OA2HRConstant.END_WORK_TIME[1])) {
									count = 1F;
								}
							} else if (leaveBean.getStartTime().equals(
									OA2HRConstant.START_WORK_TIME[1])) {
								if (leaveBean.getEndTime().equals(
										OA2HRConstant.END_WORK_TIME[0])) {
									count = 0;
								} else if (leaveBean.getEndTime().equals(
										OA2HRConstant.END_WORK_TIME[1])) {
									count = 0.5F;
								}
							}
						} else if (leaveBean.getStartTime().equals(
								OA2HRConstant.START_WORK_TIME[0])) {
							count = 1;
						} else {
							count = 0.5F;
						}
					} else if (workDay.equals(leaveBean.getEndDate())) {
						if (leaveBean.getEndTime().equals(
								OA2HRConstant.END_WORK_TIME[1])) {
							count = count + 1;
						} else {
							count = count + 0.5F;
						}
					} else {
						count = count + 1;
					}
				}
			}
			return count;
		}
	}

}
